﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
/// <summary>
/// Summary description for clsDocSQL
/// </summary>
public class clsDocSQL : DataTable
{
    private clsKetNoi m_clsKetNoi = new clsKetNoi();
    private SqlDataAdapter bo_doc_ghi;

    private string ten_bang;
    private string lenh_sql;

    //đối tượng giữ giá trị cơ sở dữ liệu trên C#
    //private DataSet m_DataSet;

    ////cầu nối giữ cơ sử dữ liệu và các đối tượng kết nối
    ////dữ liệu trên C#(DataSet, DataTable...)
    ////private SqlDataAdapter m_sqlDataAdapter;

    ////Đối tượng kết nối cơ sở dữ liệu
    //private SqlConnection m_SQLConnection;

    //public SqlConnection SQLConnection
    //{
    //    get { return m_SQLConnection; }
    //    set { m_SQLConnection = value; }
    //}
    ////public SqlDataAdapter SqlDataAdapter
    ////{
    ////    get { return m_sqlDataAdapter; }
    ////    set { m_sqlDataAdapter = value; }
    ////}
    //public DataSet DataSet
    //{
    //    get { return m_DataSet; }
    //    set { m_DataSet = value; }
    //}
    //----------------------------------------------
    public string tt_lenh_sql
    {
        get { return lenh_sql; }
        set { lenh_sql = value; }
    }
    public SqlDataAdapter tt_bo_doc_ghi
    {
        get { return bo_doc_ghi; }
        set { bo_doc_ghi = value; }
    }
    public string tt_ten_bang
    {
        get { return ten_bang; }
        set { ten_bang = value; }
    }

    public clsDocSQL()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public DataTable DocSQL(string strDoc)
    {
        try
        {
            m_clsKetNoi.OpenSQL();
            SqlDataAdapter m_SQLDataAdapter = new SqlDataAdapter(strDoc, m_clsKetNoi.SQLKetNoi);
            DataTable m_DataTable = new DataTable();
            m_SQLDataAdapter.Fill(m_DataTable);
            m_clsKetNoi.CloseSQL();
            return m_DataTable;
        }
        catch (Exception)
        {
            return null;
        }
    }
    public DataTable DocSQLStoreProcedure(string strDoc_StoreProcedure)
    {
        try
        {
            SqlCommand m_SQLCommand = new SqlCommand(strDoc_StoreProcedure, m_clsKetNoi.SQLKetNoi);
            m_SQLCommand.CommandType = CommandType.StoredProcedure;
            //class read database
            SqlDataAdapter m_SQLDataApdepter = new SqlDataAdapter(m_SQLCommand);
            DataTable m_DataTable = new DataTable();
            m_SQLDataApdepter.Fill(m_DataTable);
            return m_DataTable;
        }
        catch (Exception)
        {
            return null;
        }
    }

    //Doc co Tham So

    public DataTable DocSQLStoreProcedure1(string str_StoreProceduce, SqlParameter[] m_sqlParameter)
    {
        //clsKetNoiDatabase m_clsKNSQL = new clsKetNoiDatabase();
        try
        {
            m_clsKetNoi.OpenSQL();
            SqlCommand m_sqlCommand = new SqlCommand(str_StoreProceduce, m_clsKetNoi.SQLKetNoi);
            m_sqlCommand.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter m_sqlDataAdapter = new SqlDataAdapter(m_sqlCommand);
            if (m_sqlParameter != null)
            {
                m_sqlCommand.Parameters.AddRange(m_sqlParameter);
            }
            DataTable m_DataTable = new DataTable();
            m_sqlDataAdapter.Fill(m_DataTable);
            return m_DataTable;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            m_clsKetNoi.CloseSQL();
        }
    }
    //thêm xóa sửa
    public int Insert_Delete_Update(string str_StoreProceduce, SqlParameter[] m_sqlParameter)
    {
        try
        {
            m_clsKetNoi.OpenSQL();
            SqlCommand m_SQLCommand = new SqlCommand(str_StoreProceduce, m_clsKetNoi.SQLKetNoi);
            m_SQLCommand.CommandType = CommandType.StoredProcedure;
            if (m_sqlParameter != null)
            {
                m_SQLCommand.Parameters.AddRange(m_sqlParameter);
            }
            int m_iKQ = (int)m_SQLCommand.ExecuteNonQuery();
            return m_iKQ;
        }
        catch (Exception ex)
        {
            //throw ex;
            return -1;
        }
        finally
        {
            m_clsKetNoi.CloseSQL();
        }
    }
    public string DocKieuString(string str_StoreProceduce)
    {
        try
        {
            m_clsKetNoi.OpenSQL();
            SqlCommand m_SQLCommand = new SqlCommand(str_StoreProceduce, m_clsKetNoi.SQLKetNoi);
            m_SQLCommand.CommandType = CommandType.StoredProcedure;
            string m_iKQ = m_SQLCommand.ExecuteScalar().ToString();
            return m_iKQ;
        }
        catch (Exception ex)
        {
            //throw ex;
            return null;
        }
        finally
        {
            m_clsKetNoi.CloseSQL();
        }
    }

    //lấy dữ liệu
    //params chưa biết bao nhiêu tham số nên sử dụng params
    public DataTable LayDuLieu(string SQL, params SqlParameter[] thamso)
    {
        m_clsKetNoi.OpenSQL();
        DataTable tableTraVe = new DataTable();
        SqlCommand command = new SqlCommand(SQL, m_clsKetNoi.SQLKetNoi);
        command.Parameters.AddRange(thamso);
        tableTraVe.Load(command.ExecuteReader());
        m_clsKetNoi.CloseSQL();
        return tableTraVe;
    }

    //cập nhật dữ liệu
    public void CapNhatDuLieu(string SQL, params SqlParameter[] thamso)
    {
        m_clsKetNoi.OpenSQL();
        SqlCommand command = new SqlCommand(SQL, m_clsKetNoi.SQLKetNoi);
        command.Parameters.AddRange(thamso);
        command.ExecuteNonQuery();
        m_clsKetNoi.CloseSQL();
    }

    public void doc_bang()
    {
        tt_bo_doc_ghi.FillSchema(this, SchemaType.Mapped);
        tt_bo_doc_ghi.Fill(this);
    }

    public void Loc_dl(string dk)
    {
        this.DefaultView.RowFilter = dk;
    }

    public int Cap_Nhat(string len_CapNhat)
    {
        clsKetNoi kn = new clsKetNoi();
        int kq;
        SqlCommand bo_Lenh = new SqlCommand();
        bo_Lenh.CommandText = len_CapNhat;
        bo_Lenh.CommandType = CommandType.Text;
        bo_Lenh.Connection = kn.SQLKetNoi;
        try
        {
            kn.OpenSQL();
            kq = bo_Lenh.ExecuteNonQuery();
            kn.CloseSQL();
        }
        catch (Exception)
        {

            kq = -1;
        }
        return kq;
    }


    public object ThongKe(string lenh_ThongKe)
    {
        clsKetNoi kn = new clsKetNoi();
        object kq;
        SqlCommand bo_Lenh = new SqlCommand();
        bo_Lenh.CommandText = lenh_ThongKe;
        bo_Lenh.CommandType = CommandType.Text;
        bo_Lenh.Connection = kn.SQLKetNoi;
        try
        {
            kn.OpenSQL();
            kq = bo_Lenh.ExecuteScalar();
            kn.CloseSQL();
        }
        catch (Exception)
        {
            kq = null;
        }
        return kq;
    }

}